package net.expectx.common.single.util;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Maps;
import net.expectx.common.single.base.BaseResult;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @author lijian
 * @desc
 * @dateTime 5/29/2018 1:10 PM
 * @info 18363003321/974028417@qq.com
 */
public class ExcelUtil {
    private static String fileName;
    public static void load(String file){
        fileName=file;
    }
    public static String getCellValue(Row row, int cellNum){
        Cell cell=row.getCell(cellNum);
        try{
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                double d = cell.getNumericCellValue();
                SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
                return sdf.format( HSSFDateUtil.getJavaDate(d));
            }else {
                cell.setCellType(CellType.STRING);
                if(cell.getCellType()==CellType.STRING){
                    return (cell.getStringCellValue());
                }else if(cell.getCellType()==CellType.NUMERIC){

                    return String.valueOf((int)cell.getNumericCellValue());
                }
            }
        }catch (IllegalStateException e){
            cell.setCellType(CellType.STRING);
            if(cell.getCellType()==CellType.STRING){
                return (cell.getStringCellValue());
            }else if(cell.getCellType()==CellType.NUMERIC){
                return String.valueOf((int)cell.getNumericCellValue());
            }
        }catch (NullPointerException e){
            return "";
        }

        return "";
    }
    public static boolean judgmentHeader(Row headerRow,Map<String,String> headerTitleMap){
        boolean flag=true;
        Integer count= 0;
        for(Map.Entry entry:headerTitleMap.entrySet()){
            if(!getCellValue(headerRow,count++).equals(entry.getValue())){
                flag=false;
                break;
            }
        }
        return flag;
    }
    public static BaseResult readExcel(String sheetName, Integer firstHeaderRow, Integer firstDataRow, Map<String,String> headerTitleMap) throws Exception{
        boolean flag=true;
        String msg=null;
        Workbook wb = WorkbookFactory.create(new FileInputStream(fileName));
        Sheet sheet = wb.getSheet(sheetName);
        flag=judgmentHeader(sheet.getRow(firstHeaderRow),headerTitleMap);
        List dataList=new ArrayList();
        if (flag){
            Row row;
            int rowLength = sheet.getPhysicalNumberOfRows();
            for(Integer rowIndex=0;rowIndex<rowLength-1;rowIndex++){
                row = sheet.getRow(rowIndex+firstDataRow);
                Map<String,String>dataMap= Maps.newHashMapWithExpectedSize(7);
                Integer count= 0;
                for(Map.Entry entry:headerTitleMap.entrySet()){
                    dataMap.put(entry.getKey().toString(),getCellValue(row,count++));
                }
                dataList.add(dataMap);

            }
        }else{
            StringBuffer buffer=new StringBuffer();
            buffer.append(sheetName+"标题格式只允许");
            for(Map.Entry entry:headerTitleMap.entrySet()){
               buffer.append("["+entry.getValue()+"] ");
            }
            buffer.append("格式");
            msg=buffer.toString();
        }

        return new BaseResult(flag,msg,dataList);
    }
    public static String getCommaFormat(BigDecimal value){
        if(value.equals(BigDecimal.ZERO)){
            return "00.00";
        }else{
            return getFormat(",###.00",value);
        }

    }

    public static String getFormat(String style,BigDecimal value){
        DecimalFormat df = new DecimalFormat();
        df.applyPattern(style);
        return df.format(value.doubleValue());
    }
    public static void main(String [] args) throws Exception{
        load("C:\\Users\\Administrator\\Desktop\\test_data.xls");
        Map header=new LinkedHashMap();
        header.put("bank_Code","sleep_time");
        header.put("chinese_name","ic_card_id");

        System.out.println(JSON.toJSONString(readExcel("Sheet1",0,1,header)));
        //readExcel("Investment",0,1,TtggConstant.INVESTMENT_TITLE_MAP());
       // readExcel("Product",0,1,TtggConstant.PROJECT_TITLE_MAP());
    }
}
